//this file cleans WIA data

clear
set more off

global direc "[YOUR PATH HERE]"
global data "${direc}/data"

**JAT paths
global JT "/Users/juliaturner/Dropbox/cc_minwage"
	global output "${JT}/output/jat"
	global data "${JT}/data"
	
********************************************************************************
//load in SET data first

import excel "${data}/raw/WIASRD_01_04.xlsx", sheet(All) clear firstrow

gen statefips = .

//consolidate statefips and state names
	replace statefips = 1 if state == "Alabama"
	replace statefips = 2 if state == "Alaska"
	replace statefips = 4 if state == "Arizona"
	replace statefips = 5 if state == "Arkansas"
	replace statefips = 6 if state == "California"
	replace statefips = 8 if state == "Colorado"
	replace statefips = 9 if state == "Connecticut"
	replace statefips = 10 if state == "Delaware"
	replace statefips = 11 if state == "DistrictofColumbia"
	replace statefips = 12 if state == "Florida"
	replace statefips = 13 if state == "Georgia"
	replace statefips = 15 if state == "Hawaii"
	replace statefips = 16 if state == "Idaho"
	replace statefips = 17 if state == "Illinois"
	replace statefips = 18 if state == "Indiana"
	replace statefips = 19 if state == "Iowa"
	replace statefips = 20 if state == "Kansas"
	replace statefips = 21 if state == "Kentucky"
	replace statefips = 22 if state == "Louisiana"
	replace statefips = 23 if state == "Maine"
	replace statefips = 24 if state == "Maryland"
	replace statefips = 25 if state == "Massachusetts"
	replace statefips = 26 if state == "Michigan"
	replace statefips = 27 if state == "Minnesota"
	replace statefips = 28 if state == "Mississippi"
	replace statefips = 29 if state == "Missouri"
	replace statefips = 30 if state == "Montana"
	replace statefips = 31 if state == "Nebraska"
	replace statefips = 32 if state == "Nevada"
	replace statefips = 33 if state == "NewHampshire"
	replace statefips = 34 if state == "NewJersey"
	replace statefips = 35 if state == "NewMexico"
	replace statefips = 36 if state == "NewYork"
	replace statefips = 37 if state == "NorthCarolina"
	replace statefips = 38 if state == "NorthDakota"
	replace statefips = 39 if state == "Ohio"
	replace statefips = 40 if state == "Oklahoma"
	replace statefips = 41 if state == "Oregon"
	replace statefips = 42 if state == "Pennsylvania"
	replace statefips = 44 if state == "RhodeIsland"
	replace statefips = 45 if state == "SouthCarolina"
	replace statefips = 46 if state == "SouthDakota"
	replace statefips = 47 if state == "Tennessee"
	replace statefips = 48 if state == "Texas"
	replace statefips = 49 if state == "Utah"
	replace statefips = 50 if state == "Vermont"
	replace statefips = 51 if state == "Virginia"
	replace statefips = 53 if state == "Washington"
	replace statefips = 54 if state == "WestVirginia"
	replace statefips = 55 if state == "Wisconsin"
	replace statefips = 56 if state == "Wyoming"
	
drop state
drop if missing(statefips) //this drops Puerto Rico

gen youtht_e = younger_youth + older_youth 
rename dislocated_worker  discloc_e
rename adult adult_e

tempfile earlywia
	save `earlywia'
	
use "${data}/raw/wia_bystate.dta", clear

	drop state 
//consolidate statefips and state names
	replace state = 1 if stn == "AL"
	replace state = 2 if stn == "AK"
	replace state = 4 if stn == "AZ"
	replace state = 5 if stn == "AR"
	replace state = 6 if stn == "CA"
	replace state = 8 if stn == "CO"
	replace state = 9 if stn == "CT"
	replace state = 10 if stn == "DE"
	replace state = 11 if stn == "DC"
	replace state = 12 if stn == "FL"
	replace state = 13 if stn == "GA"
	replace state = 15 if stn == "HI"
	replace state = 16 if stn == "ID"
	replace state = 17 if stn == "IL"
	replace state = 18 if stn == "IN"
	replace state = 19 if stn == "IA"
	replace state = 20 if stn == "KS"
	replace state = 21 if stn == "KY"
	replace state = 22 if stn == "LA"
	replace state = 23 if stn == "ME"
	replace state = 24 if stn == "MD"
	replace state = 25 if stn == "MA"
	replace state = 26 if stn == "MI"
	replace state = 27 if stn == "MN"
	replace state = 28 if stn == "MS"
	replace state = 29 if stn == "MO"
	replace state = 30 if stn == "MT"
	replace state = 31 if stn == "NE"
	replace state = 32 if stn == "NV"
	replace state = 33 if stn == "NH"
	replace state = 34 if stn == "NJ"
	replace state = 35 if stn == "NM"
	replace state = 36 if stn == "NY"
	replace state = 37 if stn == "NC"
	replace state = 38 if stn == "ND"
	replace state = 39 if stn == "OH"
	replace state = 40 if stn == "OK"
	replace state = 41 if stn == "OR"
	replace state = 42 if stn == "PA"
	replace state = 44 if stn == "RI"
	replace state = 45 if stn == "SC"
	replace state = 46 if stn == "SD"
	replace state = 47 if stn == "TN"
	replace state = 48 if stn == "TX"
	replace state = 49 if stn == "UT"
	replace state = 50 if stn == "VT"
	replace state = 51 if stn == "VA"
	replace state = 53 if stn == "WA"
	replace state = 54 if stn == "WV"
	replace state = 55 if stn == "WI"
	replace state = 56 if stn == "WY"

rename (stn  state) (stateabb  statefips)
drop _merge 

	preserve
		collapse (sum)  adult_e disloc_e youtht_e, by(year)
		twoway line adult_e year || line disloc_e year || line youtht_e year
	restore
	
	pause on 
	pause
	
	merge 1:1 statefips year using `earlywia', update replace
	
	preserve
		collapse (sum)  adult_e disloc_e youtht_e, by(year)
	
		twoway line adult_e year || line disloc_e year || line youtht_e year
	restore
	

save "${data}/clean_wia.dta", replace
